﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CNotebook.Inventory;

namespace CNotebook.Purchases
{
    public class CounterManager:ConnectionManager
    {
        #region Adding/Updating ConsignmentTransfer
        public SqlException AddCounterDocument(CounterUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();
            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO OCOUNTER (DocNum, CardCode, CardName, IsBranch, CounterDate ,Remarks, TotalAmount, CreatedBy, DateCreated, ModifiedBy, DateModified) VALUES (@DocNum, @CardCode, @CardName, @IsBranch, @CounterDate ,@Remarks, @TotalAmount, @CreatedBy, @DateCreated, @ModifiedBy, @DateModified)";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocNum));
                oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@IsBranch", oUnit.IsBranch));
                oCommand.Parameters.Add(new SqlParameter("@CounterDate", oUnit.CounterDate));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@TotalAmount", oUnit.TotalAmount));

                oCommand.Parameters.Add(new SqlParameter("@DateCreated", oUnit.DateCreated));
                oCommand.Parameters.Add(new SqlParameter("@CreatedBy", oUnit.CreatedBy));
                oCommand.Parameters.Add(new SqlParameter("@DateModified", oUnit.DateModified));
                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));


                oCommand.ExecuteNonQuery();

                #region Line Items
                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {
                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "INSERT INTO COUNTER1 (DocNum, RefDocument, RefInvoice, RefDocDate, CardCode, CardName, Amount, AppliedAmount) VALUES (@DocNum, @RefDocument, @RefInvoice, @RefDocDate, @CardCode, @CardName, @Amount, @AppliedAmount)";
                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocNum));

                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocNum));
                        oCommand.Parameters.Add(new SqlParameter("@RefDocument", oRow["RefDocument"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@RefInvoice", oRow["RefInvoice"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@RefDocDate", oRow["RefDocDate"]));
                        oCommand.Parameters.Add(new SqlParameter("@CardCode", oRow["CardCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@CardName", oRow["CardName"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@AppliedAmount", oRow["AppliedAmount"].ToString()));



                        oCommand.ExecuteNonQuery();
                    }

                }
                #endregion

                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
        }

        public SqlException UpdateOrder(ConTransUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "UPDATE OCONTRN SET DocStatus=@DocStatus, PostingDate=@PostingDate, DueDate=@DueDate, DocDate=@DocDate, Location=@Location, Destination=@Destination, StoreCode=@StoreCode, CardCode=@CardCode, CardName=@CardName, ContactPerson=@ContactPerson, PaymentTerms=@PaymentTerms, Reference=@Reference, CustRefPO=@CustRefPO, BillTo=@BillTo, ShipTo=@ShipTo, SalesMan=@SalesMan, Remarks=@Remarks, GrossAmount=@GrossAmount, VATAmount=@VATAmount, WithHoldingTax=@WithHoldingTax, Discount1=@Discount1, Discount2=@Discount2, Discount3=@Discount3, Discount4=@Discount4, TotalDiscount=@TotalDiscount, NetAmount=@NetAmount, DivReference=@DivReference, DocumentCode=@DocumentCode, Division=@Division, ModifiedBy=@ModifiedBy, DateModified=GetDate()" +
                                                            " WHERE DocNum=@DocNum";

                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.Parameters.Add(new SqlParameter("@DocStatus", oUnit.DocumentStatus));
                oCommand.Parameters.Add(new SqlParameter("@PostingDate", oUnit.PostingDate));
                oCommand.Parameters.Add(new SqlParameter("@DueDate", oUnit.DueDate));
                oCommand.Parameters.Add(new SqlParameter("@DocDate", oUnit.DocDate));

                oCommand.Parameters.Add(new SqlParameter("@Destination", oUnit.Destination));
                oCommand.Parameters.Add(new SqlParameter("@Location", oUnit.Location));
                oCommand.Parameters.Add(new SqlParameter("@StoreCode", oUnit.StoreCode));
                oCommand.Parameters.Add(new SqlParameter("@CardCode", oUnit.CardCode));
                oCommand.Parameters.Add(new SqlParameter("@CardName", oUnit.CardName));
                oCommand.Parameters.Add(new SqlParameter("@ContactPerson", oUnit.ContactPerson));
                oCommand.Parameters.Add(new SqlParameter("@PaymentTerms", oUnit.PaymentTerms));

                oCommand.Parameters.Add(new SqlParameter("@Reference", oUnit.Reference));
                oCommand.Parameters.Add(new SqlParameter("@CustRefPO", oUnit.CustRefPO));
                oCommand.Parameters.Add(new SqlParameter("@BillTo", oUnit.BillTo));
                oCommand.Parameters.Add(new SqlParameter("@ShipTo", oUnit.ShipTo));
                oCommand.Parameters.Add(new SqlParameter("@SalesMan", oUnit.SalesMan));
                oCommand.Parameters.Add(new SqlParameter("@Remarks", oUnit.Remarks));
                oCommand.Parameters.Add(new SqlParameter("@GrossAmount", oUnit.GrossAmnt));
                oCommand.Parameters.Add(new SqlParameter("@VATAmount", oUnit.VATAmt));
                oCommand.Parameters.Add(new SqlParameter("@WithHoldingTax", oUnit.WithHoldingTax));
                oCommand.Parameters.Add(new SqlParameter("@Discount1", oUnit.Discount1));
                oCommand.Parameters.Add(new SqlParameter("@Discount2", oUnit.Discount2));
                oCommand.Parameters.Add(new SqlParameter("@Discount3", oUnit.Discount3));
                oCommand.Parameters.Add(new SqlParameter("@Discount4", oUnit.Discount4));
                oCommand.Parameters.Add(new SqlParameter("@TotalDiscount", oUnit.TotalDiscount));
                oCommand.Parameters.Add(new SqlParameter("@NetAmount", oUnit.NetAmount));
                oCommand.Parameters.Add(new SqlParameter("@Division", oUnit.Division));
                oCommand.Parameters.Add(new SqlParameter("@DocumentCode", oUnit.DocumentCode));
                oCommand.Parameters.Add(new SqlParameter("@DivReference", oUnit.DivReference));

                oCommand.Parameters.Add(new SqlParameter("@ModifiedBy", oUnit.ModifiedBy));

                oCommand.ExecuteNonQuery();

                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "DELETE CONTRN1 WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.ExecuteNonQuery();

                #region Line Items
                foreach (DataRow oRow in oUnit.LineItems.Rows)
                {

                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "INSERT INTO CONTRN1 (DocNum, ItemCode, ItemDesc, UoM, QtyOrder, CQtyOrder, Qty, CQty, PriceListCode, UnitPrice, IsFree, DiscPrcnt, Discount, TotalLineDisc, Amount, ItemGuid) VALUES " +
                                                                    "(@DocNum, @ItemCode, @ItemDesc, @UoM, @QtyOrder, @CQtyOrder, @Qty, @CQty, @PriceListCode, @UnitPrice, @IsFree, @DiscPrcnt, @Discount, @TotalLineDisc, @Amount, @ItemGuid)";

                        oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"]));
                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ItemDesc", oRow["ItemDesc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                        if (oRow["Qty"] == DBNull.Value)
                        {
                            int _ZeroValue = 0;
                            oRow["Qty"] = _ZeroValue;
                        }
                        oCommand.Parameters.Add(new SqlParameter("@QtyOrder", Convert.ToDouble(oRow["QtyOrder"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@CQtyOrder", Convert.ToDouble(oRow["CQtyOrder"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@Qty", Convert.ToDouble(oRow["Qty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@CQty", Convert.ToDouble(oRow["CQty"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UnitPrice", Convert.ToDouble(oRow["UnitPrice"].ToString())));
                        if (oRow["IsFree"] != DBNull.Value)
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", Convert.ToBoolean(oRow["IsFree"])));
                        }
                        else
                        {
                            oCommand.Parameters.Add(new SqlParameter("@IsFree", false));
                        }
                        oCommand.Parameters.Add(new SqlParameter("@DiscPrcnt", oRow["DiscPrcnt"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Discount", Convert.ToDouble(oRow["Discount"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@TotalLineDisc", oRow["TotalLineDisc"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@Amount", oRow["Amount"].ToString()));

                        oCommand.ExecuteNonQuery();

                    }

                }

                #endregion

                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "DELETE CONTRN2 WHERE DocNum=@DocNum";
                oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                oCommand.ExecuteNonQuery();

                #region Inventory Issued Items
                foreach (DataRow oRow in oUnit.InventoryIssuedItems.Rows)
                {
                    if (oRow.RowState != DataRowState.Deleted)
                    {
                        oCommand = new SqlCommand();
                        oCommand.Connection = this.Connection;

                        oCommand.CommandText = "INSERT INTO CONTRN2 (DocNum,WhsCode, ItemCode, ItemName, QtySrvd, UoM, LotNumber, PriceListCode, ItemGuid) VALUES  (@DocNum, @WhsCode, @ItemCode, @ItemName, @QtySrvd, @UoM, @LotNumber, @PriceListCode, @ItemGuid)";
                        oCommand.Parameters.Add(new SqlParameter("@ItemGuid", oRow["ItemGuid"]));
                        oCommand.Parameters.Add(new SqlParameter("@DocNum", oUnit.DocumentNumber));
                        oCommand.Parameters.Add(new SqlParameter("@WhsCode", oUnit.Location));
                        oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@ItemName", oRow["ItemName"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@QtySrvd", Convert.ToDouble(oRow["QtySrvd"].ToString())));
                        oCommand.Parameters.Add(new SqlParameter("@LotNumber", oRow["LotNumber"].ToString()));
                        oCommand.Parameters.Add(new SqlParameter("@PriceListCode", oRow["PriceListCode"].ToString()));


                        oCommand.ExecuteNonQuery();
                    }
                }
                #endregion

                //#region Updating Accounting Entries

                //oCommand = new SqlCommand();
                //oCommand.Connection = this.Connection;
                //oCommand.CommandText = "DELETE FROM DLN3 WHERE DOCNUM=@DOCNUM";
                //oCommand.Parameters.Add(new SqlParameter("@DOCNUM", oUnit.DocumentNumber));
                //oCommand.ExecuteNonQuery();


                //oCommand = new SqlCommand();

                //oCommand.Connection = this.Connection;
                //oCommand.CommandType = CommandType.StoredProcedure;
                //oCommand.CommandText = "SP_POST_SALESConsignmentTransfer_ENTRIES";
                //oCommand.Parameters.Add(new SqlParameter("@DOCNUM", oUnit.DocumentNumber));

                //oCommand.ExecuteNonQuery();

                //#endregion
                return null;
            }
            catch (SqlException ex)
            {
                return ex;
            }
            finally
            {
                oCommand.Dispose();
            }

        }
        #endregion
}
}
